package InterlingualHomophones::Model::DB;
use base qw/Catalyst::Base/;
use DBI;

__PACKAGE__->config(
    name => 'DB',
);

sub new {
	my( $class, $c ) = @_;
	my $this = bless {}, $class;
	
	$this->{dbh} = DBI->connect( $c->config->{db} );
	$this->create_tables;
	
	$this;
}

sub create_tables {
	my( $this ) = @_;
	$this->{dbh}->do( $_ ) for split qr/;/, join qq{\n}, <DATA>;
}

sub prepare {
	my( $this, $method, $statement ) = @_;
	
	$this->{prepared}->{$method} = $this->{dbh}->prepare( $statement )
		|| die $this->{dbh}->errstr	
			unless $this->{prepared}->{$method};
	
	$this->{prepared}->{$method};
}

sub insert {
	my( $this, $relation, $tuples ) = @_;
	
	my @dimentions = keys %{ $tuples->[0] };
	my $dimentinos = join q{, }, @dimentions;
	my $placehldrs = join q{,}, map {'?'} @dimentions;
	
	my $sth = $this->{dbh}->prepare( qq{
		INSERT INTO $relation ($dimentinos)
		VALUES ($placehldrs)
	} ) || die $this->{dbh}->errstr;
	
	for my $tuple ( @$tuples ) {
		$sth->execute( map { $tuple->{$_} } @dimentions )
			 || die $this->{dbh}->errstr;
	}
}

sub foreach {
	my $do_what = pop;
	my( $this, $sth, @args ) = @_;
	$sth->execute( @args ) || die $this->{dbh}->errstr;
	while( my $ref = $what->fetchrow_hashref ) { $do_what->( $ref )	}
}

sub getall {
	my( $this, $sth, @args ) = @_;
	$sth->execute( @args ) || die $this->{dbh}->errstr;
	$sth->fetchall_arrayref({});
}

#########################################################################
#	END OF ABSTRACTION
# TODO: abstract into separate class and document
#########################################################################

sub phrases_all {
	my( $this ) = @_;
	
	$this->prepare( phrases_all => q{
			SELECT language, text
			FROM PhoneticApproximations
		UNION
			SELECT language1 as language, text1 as text
			FROM SemanticApproximations
		UNION
			SELECT language2 as language, text2 as text
			FROM SemanticApproximations
	} );
}

# args: language, text
sub phrases_all_except_one {
	my( $this ) = @_;
	
	$this->prepare( phrases_all_except_one => q{
			SELECT language, text
			FROM PhoneticApproximations
		UNION
			SELECT language1 as language, text1 as text
			FROM SemanticApproximations
		UNION
			SELECT language2 as language, text2 as text
			FROM SemanticApproximations
		EXCEPT
			SELECT ?, ?
	} );
}

# args: language, text
sub homophones_all {
	my( $this ) = @_;
	
	$this->prepare( homophones_all => q{
		SELECT DISTINCT language, text
		FROM PhoneticApproximations
		WHERE phonetic IN (
				SELECT phonetic
				FROM PhoneticApproximations
				WHERE language = ? AND text = ? )
	} );
}

1;

__DATA__
--
-- Interlingual Homophones Relational Database Schema Definition
--

CREATE TABLE IF NOT EXISTS PhoneticApproximations (
    language VARCHAR(255) NOT NULL,
    text     VARCHAR(255) NOT NULL,
    phonetic VARCHAR(255) NOT NULL,
    CONSTRAINT pk PRIMARY KEY (language, text, phonetic)
);

CREATE TABLE IF NOT EXISTS SemanticApproximations (
    language1 VARCHAR(255) NOT NULL,
    text1     VARCHAR(255) NOT NULL,
    language2 VARCHAR(255) NOT NULL,
    text2     VARCHAR(255) NOT NULL,
    CONSTRAINT pk PRIMARY KEY (language1, text1, language2, text2)
);
